package com.rdc.example.dao.impl;

import com.rdc.example.dao.UserDao;
import com.rdc.example.entity.User;
import com.rdc.example.util.HashUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

/**
 * @author duck
 * @description User实体类dao层接口实现类
 */
public class UserDaoImpl implements UserDao {

    /*public static void main(String[] args) throws Exception {
        UserDaoImpl userDao = new UserDaoImpl();
        User user=new User();
        System.out.println(userDao.getUserByUserName("duck"));

    }*/

    @Override
    public boolean login(String userName, String password) throws Exception {
        //只能传入密码原值，在与数据库中的加密值比较
        Connection conn =null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "select password_hash from user where user_name=?" ;
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                //返回比较结果
                return HashUtil.getSaltverifyMD5(password,rs.getString("password_hash"));
            }
            else {
                return false;
            }
        } catch (Exception e) {
            throw e;
        }finally {
            //释放资源
            closeResources(rs, pstmt, conn);
        }
    }

    @Override
    public boolean insert(User user) throws Exception {
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn =getConnection();
            //System.out.println(user);
            String sql ="insert into `user` (user_name, password_hash, phone, email, avatar_path, user_type, wallet, vip_end_at) values (?,?,?,?,?,?,?,?);";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            setUser(pstmt,user);
            //以影响行数row判断并返回注册结果
            int row = pstmt.executeUpdate();//70  行
            return row > 0;
        } catch (Exception e) {
            throw e;
        }finally {
            //释放资源
            closeResources(null,pstmt,conn);
        }
    }

    @Override
    public boolean checkName(String name) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = getConnection();
            // SQL语句，查询数据库中是否存在该用户名
            String sql = "select count(*) from `user` where user_name =?";
            // 执行SQL语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setString(1, name);
            // 执行查询并获取结果
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                // 如果查询到记录数大于0，说明用户名存在，返回true；否则返回false
                return count > 0;
            }
            return false;
        } catch (SQLException e) {
            throw e;
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
    }



    @Override
    public boolean update(User user) throws Exception {
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn =getConnection();
            String sql ="update `user` set user_name = ?, password_hash = ?, email = ?, phone = ?, avatar_path = ?, user_type = ?, wallet = ?, vip_end_at = ? where id = ?";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            setUser(pstmt,user);
            pstmt.setInt(9, user.getId());
            //以影响行数row判断并返回注册结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw e;
        }finally {
            //释放资源
            closeResources(null,pstmt,conn);
        }
    }

    @Override
    public User getUserById(Integer id) throws Exception {
        // 进行数据库查询操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        ResultSet rs = null;
        User user = null;
        try{//获取连接
            conn=getConnection();
            String sql = "select * from user where id=?";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            //封装user数据
            if (rs.next()) {
                user = getUser(rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs,pstmt,conn);
        }
        return user;
    }

    @Override
    public User getUserByUserName(String userName) throws Exception {
        // 进行数据库查询操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        ResultSet rs = null;
        User user = null;
        try{//获取连接
            conn=getConnection();
            String sql = "select * from user where user_name=?";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            //封装user数据
            if (rs.next()) {
                user = getUser(rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs,pstmt,conn);
        }
        return user;
    }

    @Override
    public boolean updateUserType(Integer id, Integer type) throws Exception {
        if (type!=1&&type!=2&&type!=3) {
            return false;
        }
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn =getConnection();
            //1 普通用户 2 音乐人 3 管理员
            String sql ="update `user` set user_type = ? where id = ?";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setInt(1, type);
            pstmt.setInt(2, id);
            //以影响行数row判断并返回注册结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } finally {
            //释放资源
            closeResources(null,pstmt,conn);
        }
    }

    @Override
    public boolean updateUserWallet(Integer userId, BigDecimal changeValue) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            // SQL语句，更新用户的wallet字段，将原金额加上要增加的金额
            String sql = "update `user` set wallet = wallet + ? where id =?";
            // 执行sql语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setBigDecimal(1, changeValue);
            pstmt.setInt(2, userId);
            // 以影响行数row判断并返回更新结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw e;
        } finally {
            // 释放资源
            closeResources(null,pstmt, conn);
        }
    }

    @Override
    public boolean updateUserVipEndAt(Integer userId, LocalDateTime vipEndAt) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            //更新vipEndAt
            String sql = "update `user` set vip_end_at=? where id =?";
            // 执行sql语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setTimestamp(1, Timestamp.valueOf(vipEndAt));
            pstmt.setInt(2, userId);
            // 以影响行数row判断并返回更新结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw e;
        } finally {
            // 释放资源
            closeResources(null,pstmt, conn);
        }
    }



    @Override
    public List<User> listUser() {
        // 进行数据库查询操作
        return null;
    }

    @Override
    public Map<Integer, User> getUsersByIds(Set<Integer> userIds) {
        Map<Integer, User> userMap = new HashMap<>();
        if (userIds == null || userIds.isEmpty()) {
            return userMap;
        }
        String sql = "select id, user_name from user where id in (" +
                userIds.stream().map(String::valueOf).collect(Collectors.joining(",")) + ")";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                userMap.put(user.getId(), user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return userMap;
    }

    @Override
    public List<User> searchUsersByUsername(String keyword, Integer pageBegin, Integer pageSize) throws Exception {
        List<User> userList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select * from `user` where user_name like ? limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                // 循环获取结果
                userList.add(getUser(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return userList;
    }

    @Override
    public Integer countSearchUsersByUsername(String keyword) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 两个 用户搜索排序的方法公用这个方法
            String sql = "select count(*) from `user` where user_name like ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }


    private User getUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUserName(rs.getString("user_name"));
        user.setPasswordHash(rs.getString("password_hash"));
        user.setPhone(rs.getString("phone"));
        user.setEmail(rs.getString("email"));
        user.setAvatarPath(rs.getString("avatar_path"));
        user.setUserType(rs.getInt("user_type"));
        user.setWallet(rs.getBigDecimal("wallet"));
        String vipEndAt = rs.getString("vip_end_at");
        if(vipEndAt != null) {
            //防空指针
            //user.setVipEndAt(LocalDateTime.parse(vipEndAt));
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            user.setVipEndAt(LocalDateTime.parse(vipEndAt, formatter));
        }
        user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return user;
    }

    private void setUser(PreparedStatement pstmt,User user) throws SQLException {
        //对null处理
        if (user.getUserName() != null) {
            pstmt.setString(1, user.getUserName());
        } else {
            pstmt.setNull(1, java.sql.Types.VARCHAR);
        }
        if (user.getPasswordHash() != null) {
            pstmt.setString(2, user.getPasswordHash());
        } else {
            pstmt.setNull(2, java.sql.Types.VARCHAR);
        }
        if (user.getPhone() != null) {
            pstmt.setString(3, user.getPhone());
        } else {
            pstmt.setNull(3, java.sql.Types.VARCHAR);
        }
        if (user.getEmail() != null) {
            pstmt.setString(4, user.getEmail());
        } else {
            pstmt.setNull(4, java.sql.Types.VARCHAR);
        }
        if (user.getAvatarPath() != null) {
            pstmt.setString(5, user.getAvatarPath());
        } else {
            pstmt.setNull(5, java.sql.Types.VARCHAR);
        }
        if (user.getUserType() != null) {
            pstmt.setInt(6, user.getUserType());
        } else {
            //默认 1：普通用户
            pstmt.setInt(6, 1);
        }
        if (user.getWallet() != null) {
            pstmt.setBigDecimal(7, user.getWallet());
        } else {
            //默认0.00
            pstmt.setBigDecimal(7, BigDecimal.valueOf(0.00));
        }
        if (user.getVipEndAt() != null) {
            pstmt.setTimestamp(8, Timestamp.valueOf(user.getVipEndAt()));
        } else {
            pstmt.setNull(8, java.sql.Types.TIMESTAMP);
        }
    }

}
